
package com.scs.application.modules.upms.service.impl;

import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.parser.SqlInfo;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.baomidou.mybatisplus.extension.plugins.pagination.DialectFactory;
import com.baomidou.mybatisplus.extension.plugins.pagination.DialectModel;
import com.baomidou.mybatisplus.extension.plugins.pagination.dialects.MySqlDialect;
import com.baomidou.mybatisplus.extension.toolkit.SqlParserUtils;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.scs.application.consts.GlobalConsts;
import com.scs.application.core.exceptions.BusinessException;
import com.scs.application.core.model.PageRequest;
import com.scs.application.core.service.BaseServiceImpl;
import com.scs.application.core.utils.BeanUtils;
import com.scs.application.core.utils.FreeMarkers;
import com.scs.application.core.utils.NamedParamSqlUtils;
import com.scs.application.core.utils.StringUtils;
import com.scs.application.modules.upms.consts.DictConsts;
import com.scs.application.modules.upms.entity.ReportChartField;
import com.scs.application.modules.upms.entity.ReportConfig;
import com.scs.application.modules.upms.entity.ReportGridField;
import com.scs.application.modules.upms.mapper.ReportConfigMapper;
import com.scs.application.modules.upms.report.handler.ReportParamHandler;
import com.scs.application.modules.upms.request.ReportConfigQueryRequest;
import com.scs.application.modules.upms.service.GenDatasourceService;
import com.scs.application.modules.upms.service.ReportChartFieldService;
import com.scs.application.modules.upms.service.ReportConfigService;
import com.scs.application.modules.upms.service.ReportGridFieldService;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.sql.DataSource;
import java.io.Serializable;
import java.sql.ResultSetMetaData;
import java.util.Collections;
import java.util.List;
import java.util.Map;

/**
 * 报表配置 service 实现
 */
@Slf4j
@AllArgsConstructor
@Service
@Transactional(rollbackFor = Exception.class)
public class ReportConfigServiceImpl extends BaseServiceImpl<ReportConfigMapper, ReportConfig> implements ReportConfigService {

    private final ReportGridFieldService reportGridFieldService;
    private final ReportChartFieldService reportChartFieldService;

    private final GenDatasourceService genDatasourceService;


    private List<ReportParamHandler> paramHandlers;

    @Autowired(required = false)
    public void setParamHandlers(List<ReportParamHandler> paramHandlers) {
        this.paramHandlers = paramHandlers;
    }

    @Override
    public boolean removeCascadeById(Serializable id) {
        reportGridFieldService.remove(Wrappers.<ReportGridField>query().eq("report_id", id));
        reportChartFieldService.remove(Wrappers.<ReportChartField>query().eq("report_id", id));
        return super.removeById(id);
    }

    @Override
    public List<ReportConfig> list(ReportConfigQueryRequest request) {
        List<ReportConfig> list = this.list(Wrappers.<ReportConfig>query()
                .like(StringUtils.isNotBlank(request.getCode()), "code", request.getCode())
                .like(StringUtils.isNotBlank(request.getName()), "name", request.getName())
                .orderByAsc("code")
        );
        return list;
    }

    @Override
    public IPage<ReportConfig> page(ReportConfigQueryRequest request) {
        IPage page = this.page(request.getPage(), Wrappers.<ReportConfig>query()
                .like(StringUtils.isNotBlank(request.getCode()), "code", request.getCode())
                .like(StringUtils.isNotBlank(request.getName()), "name", request.getName())
                .orderByAsc("code")
        );
        return page;
    }

    @Override
    public Map<String, Object> fetchData(String id, Map<String, Object> params) {
        return this.fetchData(id, true, params);
    }

    @Override
    public Map<String, Object> fetchData(String id, boolean forcePaging, Map<String, Object> params) {
        ReportConfig config = this.getById(id);
        if (config == null) {
            throw new BusinessException("报表参数错误，请确认");
        }
        Map<String, Object> resultMap = Maps.newHashMap();

        String types = config.getTypes();
        // 包含表格字段
        if (types.indexOf(DictConsts.REPORT_CONFIG_TYPES_GRID) != -1) {
            resultMap.put("gridFields", reportGridFieldService.list(Wrappers.<ReportGridField>query().eq("report_id", config.getId()).orderByAsc("sort")));
        }

        // 包含图表字段
        if (types.indexOf(DictConsts.REPORT_CONFIG_TYPES_LINE) != -1
                || types.indexOf(DictConsts.REPORT_CONFIG_TYPES_BAR) != -1
                || types.indexOf(DictConsts.REPORT_CONFIG_TYPES_PIE) != -1) {
            resultMap.put("chartFields", reportChartFieldService.list(Wrappers.<ReportChartField>query().eq("report_id", config.getId()).orderByAsc("sort")));
        }
        resultMap.put("config", config);

        String source = config.getSource();

        if (DictConsts.REPORT_CONFIG_SOURCE_SQL.equalsIgnoreCase(source)) {
            FetchResult result = fetchSqlData(config, forcePaging, params);
            resultMap.put("data", result.getData());
            resultMap.put("totalCount", result.getTotalCount());
        } else if (DictConsts.REPORT_CONFIG_SOURCE_JSON.equalsIgnoreCase(source)) {
            resultMap.put("data", config.getDataJson());
        } else if (DictConsts.REPORT_CONFIG_SOURCE_URL.equalsIgnoreCase(source)) {

        }
        config.setDataSql(null).setDataJson(null).setDataUrl(null);
        return resultMap;
    }

    /**
     * 解析SQL，获得数据
     *
     * @param config
     * @param forcePaging 强制分页
     * @param params
     * @return
     */
    private FetchResult fetchSqlData(ReportConfig config, boolean forcePaging, Map<String, Object> params) {
        DataSource datasource = genDatasourceService.getRealDatasource(config.getDatasourceId());


        JdbcTemplate jdbcTemplate = new JdbcTemplate(datasource);

        NamedParamSqlUtils sqlUtils = new NamedParamSqlUtils();
        String selectSql = config.getDataSql();

        // 对查询SQL进行 模板渲染
        selectSql = FreeMarkers.renderString(selectSql, params);
        selectSql = sqlUtils.parseSql(selectSql);

        Map<String, Object> handledParams = getHandledParams(sqlUtils, params);

        FetchResult result = new FetchResult();

        // 构建分页SQL
        if (forcePaging && GlobalConsts.YES_STR.equals(config.getIsPaging())) {

            SqlInfo sqlInfo = SqlParserUtils.getOptimizeCountSql(true, null, selectSql);
            Long totalCount = jdbcTemplate.query(sqlInfo.getSql(), ps -> {
                sqlUtils.fillParameters(ps, handledParams);
            }, rs -> rs.next() ? rs.getLong(1) : 0);
            result.setTotalCount(totalCount);
            log.debug("=======================query total count sql=======================");
            log.debug("SQL: {}", sqlInfo.getSql());
            log.debug("Params: {}", handledParams);
            log.debug("=======================query total count sql=======================");
            if (totalCount == 0) {
                result.setData(Collections.emptyList());
                return result;
            }

            PageRequest pageRequest = new PageRequest(Integer.parseInt((String) params.getOrDefault("currPage", "1")), Integer.parseInt((String) params.getOrDefault("pageSize", "10")));
            IPage page = pageRequest.getPage();
            DialectModel model = DialectFactory.buildPaginationSql(page, selectSql, DbType.MYSQL, MySqlDialect.class.getName());
            String pagingSql = model.getDialectSql().replaceAll("LIMIT \\?,\\?",
                    String.format("LIMIT %d,%d", (pageRequest.getCurrPage() - 1) * pageRequest.getPageSize(), pageRequest.getPageSize())
            );
            selectSql = pagingSql;

        }


        List<Map<String, Object>> data = jdbcTemplate.query(selectSql, ps -> {
            sqlUtils.fillParameters(ps, handledParams);
        }, (rs, rowNum) -> {
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();
            Map<String, Object> map = Maps.newHashMapWithExpectedSize(columnCount);

            for (int i = 1; i <= columnCount; i++) {
                map.put(metaData.getColumnLabel(i), rs.getObject(i));
            }
            return map;
        });
        result.setData(data);
        result.setTotalCount(result.getTotalCount() == null ? data.size() : result.getTotalCount());
        log.debug("=======================query data sql=======================");
        log.debug("SQL: {}", selectSql);
        log.debug("Params: {}", handledParams);
        log.debug("=======================query data sql=======================");

        return result;
    }


    @Override
    public List<Map<String, Object>> sqlParse(String sql, String datasourceId, Map<String, Object> params) {
        if (StringUtils.isBlank(sql) || StringUtils.isBlank(datasourceId)) {
            throw new BusinessException("参数错误，请确认");
        }
        DataSource datasource = genDatasourceService.getRealDatasource(datasourceId);

        JdbcTemplate jdbcTemplate = new JdbcTemplate(datasource);
        jdbcTemplate.setFetchSize(0);
        jdbcTemplate.setMaxRows(0);


        NamedParamSqlUtils sqlUtils = new NamedParamSqlUtils();
        String selectSql = sql;

        // 对查询SQL进行 模板渲染
        selectSql = FreeMarkers.renderString(selectSql, params);
        selectSql = sqlUtils.parseSql(selectSql);

        Map<String, Object> handledParams = getHandledParams(sqlUtils, params);

//        PageRequest pageRequest = new PageRequest(1, 1);
//        DialectModel model = DialectFactory.buildPaginationSql(pageRequest.getPage(), selectSql, DbType.MYSQL, MySqlDialect.class.getName());
//        String pagingSql = model.getDialectSql().replaceAll("LIMIT \\?,\\?", String.format("LIMIT %d,%d", 0, 1));
//        selectSql = pagingSql;

        List<Map<String, Object>> result = Lists.newArrayList();
        jdbcTemplate.query(selectSql, ps -> {
            sqlUtils.fillParameters(ps, handledParams);
        }, rs -> {
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();

            for (int i = 1; i <= columnCount; i++) {
                Map<String, Object> map = Maps.newHashMapWithExpectedSize(columnCount);
                map.put("columnLabel", metaData.getColumnLabel(i));
                map.put("sort", i * 10);
                result.add(map);
            }
            return null;
        });
        return result;

    }

    @Override
    public ReportConfig copy(String id) {

        ReportConfig raw = this.getById(id);
        if (raw == null) {
            return null;
        }

        final ReportConfig copy = new ReportConfig();
        BeanUtils.copyProperties(raw, copy);
        copy.setName(raw.getName() + "-副本").setCode(raw.getCode() + "-副本").setId(null);
        this.save(copy);

        List<ReportGridField> gridFields = reportGridFieldService.list(Wrappers.<ReportGridField>query().eq("report_id", raw.getId()));
        List<ReportChartField> chartFields = reportChartFieldService.list(Wrappers.<ReportChartField>query().eq("report_id", raw.getId()));

        gridFields.forEach(o -> o.setReportId(copy.getId()).setReportCode(copy.getCode()).setId(null));
        chartFields.forEach(o -> o.setReportId(copy.getId()).setReportCode(copy.getCode()).setId(null));
        if (!gridFields.isEmpty()) {
            reportGridFieldService.saveBatch(gridFields);
        }
        if (!chartFields.isEmpty()) {
            reportChartFieldService.saveBatch(chartFields);
        }

        return copy;
    }

    /**
     * 获取处理过后的参数
     *
     * @param params
     * @return
     */
    private Map<String, Object> getHandledParams(NamedParamSqlUtils sqlUtils, Map<String, Object> params) {
        Map<String, Object> handledParams = null;
        if (params == null) {
            params = Maps.newHashMap();
        }
        handledParams = params;

        if (paramHandlers == null) {
            return handledParams;
        }

        // sql解析后的参数名
        Map<Integer, String> paramsMap = sqlUtils.getParamsMap();
        for (Integer index : paramsMap.keySet()) {
            String paramName = paramsMap.get(index);
            if (!params.containsKey(paramName)) {
                params.put(paramName, null);
            }
        }

        for (String key : params.keySet()) {
            for (ReportParamHandler handler : paramHandlers) {
                if (handler.getNeedHandleFields().contains(key)) {
                    handledParams.put(key, handler.handle(key, params.get(key)));
                }
            }
        }
        return handledParams;
    }

    /**
     * sql提取的结果
     */
    @Data
    private static class FetchResult {
        private List<Map<String, Object>> data;
        private Long totalCount;
    }

}
